from flask import Blueprint, request, jsonify, make_response
from logging import FileHandler
import logging
from flask import current_app as app
from utils.db import db1
import json
import datetime
from utils.myfile import myf
import os
from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
# import xlsxwriter
import datetime
import mimetypes
from utils.es import Es
import xlsxwriter

bp_compan = Blueprint('compan', __name__)


@bp_compan.route('/compan')
async def compan():
    str = "请求IP:" + request.remote_addr + '------' + "请求的url:" + request.url + '\n'
    app.logger.error(str)
    return 'ok'


def get_data():
    data = request.data
    if data:
        data = json.loads(data)
    else:
        data = request.form
    return data


def xtree(data):
    list = []
    dict = {}
    if data:
        # for k in data:
        #     k['label'] = k['name']
        for i in data:
            i['label'] = i['name']
            i['value'] = i['id']
            dict[i['id']] = i

        for j in data:
            if j['pid'] == 0:
                j['label'] = j['name']
                list.append(j)
            else:
                if 'children' not in dict[j['pid']]:
                    dict[j['pid']]['children'] = []
                dict[j['pid']]['children'].append(j)
        return list


@bp_compan.route('/add', methods=['POST', 'GET'])
def add():
    if request.method == 'POST':
        data = get_data()
        createtime = datetime.datetime.strftime(datetime.datetime.now(), "%Y%m%d%H%M%S")
        sql1 = 'insert into company(name,pid,zztype,zzcode,createman,createtime) values("%s",%d,%d,"%s","%s","%s")' % (
            data['name'], int(data['pid']), int(data['zztype']), data['zzcode'], 'admin', createtime)
        db1.update(sql1)
        return jsonify({'code': 200})
    elif request.method == 'GET':
        sql = 'select * from company'
        res = db1.findall(sql)
        list = xtree(res)
        return jsonify({'code': 200, 'list': list})


import time


@bp_compan.route('/gangwei', methods=['GET', 'POST'])
def gangwei():
    if request.method == 'GET':
        sql = 'select * from works'
        res = db1.findall(sql)
        return jsonify({'code': 200, 'list': res})
    elif request.method == 'POST':
        data = get_data()
        createtime = datetime.datetime.strftime(datetime.datetime.now(), "%Y%m%d%H%M%S")
        sql = 'insert into works(name,gwcode,content,createman,createtime) values("%s","%s","%s","%s","%s")' % (
            data['name'], data['gwcode'], data['content'], "admin", createtime)
        db1.update(sql)
        return jsonify({'code': 200})


@bp_compan.route('/addpic1', methods=['POST'])
def addpic():
    if request.method == 'POST':
        file = request.files['file']
        newname = myf.rename(file.filename)
        file.save(os.path.join('./static/upload/' + newname))
        print(newname)
        return jsonify({'code': 200, 'pic': newname})


# 文件上传
@bp_compan.route('/upload', methods=['POST'])
def upload():
    if request.method == 'POST':
        file = request.files['file']
        # 重命名
        newname = myf.rename(file.filename)
        # 上传指定目录
        file.save(os.path.join('./static/file/' + newname))
        name = './static/file/' + newname
        bk = open_workbook(name, encoding_override='utf-8')
        sh = bk.sheets()[0]
        nrows = sh.nrows
        ncols = sh.ncols
        for i in range(2, nrows):
            row_date = sh.row_values(i)
            for j in range(0, ncols):
                print(row_date[j])

        return jsonify({'code': 200})


# 文件导出
@bp_compan.route('/download', methods=['POST', 'GET'])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    table.write(0, 0, "资产编号")
    table.write(0, 1, "资产名称")

    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file


@bp_compan.route('/esinsert')
def esinsert():
    # 调用封装的方法  名称和状态
    es = Es('testtable', 'test_type')
    sql1 = 'select * from company'
    res = db1.findall(sql1)
    # 循环调用方法加入
    for i in res:
        es.insert(i)
    return 'ok'


@bp_compan.route('/esfind')
def esfind():
    es = Es('testtable', 'test_type')
    # 设置查询条件
    body = {
        "match": {
            "name": "唐养猪协会"}
    }
    data = es.find(body)
    print(data['hits']['hits'])
    return 'ok'
